﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using MoonLight.LibCommon;

namespace PVAutoTest.LibDataBase
{
    class ExcelOperator
    {
        public int OpenExcel(string filename, string sheet, ref DataSet ds)
        {
            int result = 0;

            string conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + "; Extended Properties=Excel 12.0;";
            string sql = "select * from [" + sheet + "$]";
            OleDbDataAdapter excel_adptr = new OleDbDataAdapter(sql, conn_str);
            
            try
            {
                result = excel_adptr.Fill(ds);
            }
            catch (Exception ex)
            {
                Logger.WriteLog("excute sql[" + sql + "] failed, [" + ex.Message + "]");
                result = -1;
            }
            return result;
        }

        public int CreateExcel(string filename, DataSet ds)
        {
            int result = 0;
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return -1;
            int col_count = ds.Tables[0].Columns.Count;
            int row_count = ds.Tables[0].Rows.Count;
            int start_col = 1;
            int start_row = 2;

            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Application();
                Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks;
                Missing miss = Missing.Value;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(miss);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                Microsoft.Office.Interop.Excel.Range cell = null;

                excel.ScreenUpdating = false; //关屏幕更新
                excel.Visible = false; //禁止显示excel
                excel.DisplayAlerts = false; //禁止警告

                //添加列
                for (int i = 0; i < col_count; i++)
                {
                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[start_row - 1, i + start_col]).FormulaR1C1 = ds.Tables[0].Columns[i].ToString();
                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[start_row - 1, i + start_col]).Font.Bold = true; //粗体
                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[start_row - 1, i + start_col]).Borders.LineStyle = 1; //设置边框
                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[start_row - 1, i + start_col]).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //水平居中
                    ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[start_row - 1, i + start_col]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //垂直居中
                }

                //添加行记录　　　　　　　　　　
                for (int i = 0; i < row_count; i++)
                {
                    for (int j = 0; j < col_count; j++)
                    {
                        //((Excel.Range)worksheet.Cells[i + start_row, j + start_col]).NumberFormat = "#";
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + start_row, j + start_col]).FormulaR1C1 = ds.Tables[0].Rows[i].ItemArray.GetValue(j);
                        ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i + start_row, j + start_col]).Borders.LineStyle = 1;
                    }
                }

                Microsoft.Office.Interop.Excel.Range range_time;
                range_time = worksheet.get_Range("F2", "G" + row_count + 1);
                range_time.NumberFormat = "yyyy-mm-dd h:mm:ss";

                cell = worksheet.get_Range(excel.Cells[start_row, start_col], excel.Cells[row_count + start_row, start_col + 2]); //get_Range方法为区域选择
                cell.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                cell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                cell = worksheet.get_Range(excel.Cells[1, 1], excel.Cells[row_count, col_count]);
                cell.Columns.AutoFit();

                excel.ScreenUpdating = true;
                workbook.SaveAs(filename, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss); //另存为

                excel.Quit();
                killexcel(excel); //结束excel进程
            }
            catch (Exception ex)
            {
                Logger.WriteConsole("create excel report fail, message[" + ex.Message.ToString() + "]");
                result = -1;
            }

            return result;
        }

        #region for kill excel process
        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
        private void killexcel(Microsoft.Office.Interop.Excel.Application excel)
        {
            try
            {
                if (excel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(excel.Hwnd), out lpdwProcessId);
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog("kill excel process err, message[" + ex.Message + "]");
            }
        }
        #endregion 
    }
}
